﻿

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[fares_viewStocuri]
	@IDArticol int, 
	@LaData datetime
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
		
	SELECT     s.IDArticol, s.Pret, s.IDVarinataArticol, s.IDDepartament, COALESCE (stin.Intrari, 0) AS Intrari, COALESCE (stout.Iesiri, 0) AS Iesiri, s.StocInitial, s.StocPrecedent, 
                      COALESCE (stin.Intrari, 0) - COALESCE (stout.Iesiri, 0) + s.StocInitial + s.StocPrecedent AS Stoc
	FROM         dbo.Stocuri AS s LEFT OUTER JOIN
          (SELECT     ddin.IDArticol, SUM(ddin.Cantitate) AS Intrari
            FROM          dbo.DocumenteDetaliu AS ddin INNER JOIN
                                   dbo.Documente AS din ON ddin.IDDocument = din.IDDocument INNER JOIN
                                   dbo.TipDocument AS td ON din.IDTipDocument = td.IDTipDocument
            WHERE      (td.ModificaStoc = 1) AND (td.TipOperatie LIKE '%I%' AND din.DataDoc <= @LaData AND ddin.IDArticol = @IDArticol)
            GROUP BY ddin.IDArticol) AS stin ON stin.IDArticol = s.IDArticol LEFT OUTER JOIN
          (SELECT     ddin.IDArticol, SUM(ddin.Cantitate) AS Iesiri
            FROM          dbo.DocumenteDetaliu AS ddin INNER JOIN
                                   dbo.Documente AS din ON ddin.IDDocument = din.IDDocument INNER JOIN
                                   dbo.TipDocument AS td ON din.IDTipDocument = td.IDTipDocument
            WHERE      (td.ModificaStoc = 1) AND (td.TipOperatie LIKE '%O%' AND din.DataDoc <= @LaData AND ddin.IDArticol = @IDArticol)
            GROUP BY ddin.IDArticol) AS stout ON stout.IDArticol = s.IDArticol
END